#!/usr/bin/env bash
source /etc/profile

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
create TEMPORARY table res_notoil
select SUBSTR(transaction_date,1,16) minutes,station_id stationid,count(DISTINCT transaction_id) notoilnumber,sum(X.item_value) notoilmoney
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value
from ( SELECT transaction_date,transaction_id,station_id from realtime.transactions where substr(transaction_date,1,10)='$dt' ) as Z
join realtime.transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id)AS X
group by SUBSTR(transaction_date,1,16),station_id;

create TEMPORARY table mid_notoilRate
select substr(transaction_date,1,13) minutes,station_id stationid,count(*) tradenumber
from ( SELECT transaction_date,transaction_id,station_id from realtime.transactions where substr(transaction_date,1,10)='$dt' ) as Z
group by substr(transaction_date,1,13),station_id;

select A.houra,A.stationID,A.numbera,B.numberb
FROM
(select SUBSTR(minutes,1,13)AS houra,stationID,sum(notoilNumber)AS numbera
from res_notoil
group by SUBSTR(minutes,1,13),stationID) AS A
JOIN
(select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
from mid_notoilRate
group by substr(minutes,1,13),stationID) AS B
ON A.houra=B.hourb and A.stationID=B.stationID;
EOF